#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
    json_to_excel.py
    ~~~~~~~
    openpyxl==3.0.0

    json文件转换为excel文件(xlsx)

    注:最多容纳1048576行数据

    :author: Yang Yongkeng
    :copyright: (c) 2021, Tungee
    :date created: 2021-04-20
    :python version: 2.7
"""

import os
import sys
import json

import openpyxl
from openpyxl.styles import NamedStyle, Font, Alignment, Border, Side
from openpyxl.styles.colors import BLACK
from openpyxl.utils import get_column_letter


font = Font(
    name='宋体',
    color=BLACK,
    outline=True,
    size=9,
)
title_font = Font(
    name='宋体',
    color=BLACK,
    outline=True,
    size=9,
    b=True
)
alignment = Alignment(
    horizontal='left',
    vertical='center',
)
border = Border(
    left=Side(border_style='thin'),
    right=Side(border_style='thin'),
    top=Side(border_style='thin'),
    bottom=Side(border_style='thin'),
)

# 表格样式
default_style = NamedStyle(name='default', font=font, alignment=alignment, border=border)
title_style = NamedStyle(name='title', font=title_font, alignment=alignment, border=border)


filter_fields = ['ossUrl', 'oss_url']
contact_secret_fields = []
enterprise_secret_fields = ['name', 'enterpriseName', '']


def tran_to_excel(src, dst=''):
    if not os.path.exists(src):
        print('file(%s) not fount' % src)
        return

    if not dst:
        dst = src + '.xlsx'
    print(f"dst file:{dst}")
    sheet_name = src.split('/')[-1].split('.')[0]
    workbook = openpyxl.Workbook()

    ws = workbook.active

    title_list = []
    # 首行下标为1
    row = 1
    max_len = {}

    # with open(src, 'r',encoding='utf-8',errors="ignore") as fin:
    with open(src, 'r',encoding='utf-8') as fin:
        for line in fin:
            line =  line.strip()
            if not line:
                continue
            if row == 1048576:
                break
            # 第二行开始写
            row += 1
            if row%100==0:
                print(f'current row :{row}')
            # if len(line) >= 32767:  # 如果行字长超过excel单元格限制长度,结束本次循环
            #     continue
            try:
                doc = json.loads(line)
            except:
                print(line)
                continue
            for key, value in doc.items():
                if key in filter_fields:
                    continue
                if isinstance(value, (list, dict)):
                    value = json.dumps(value, ensure_ascii=False)
                elif not isinstance(value, str):
                    value = repr(value)
                if key not in title_list:
                    title_list.append(key)
                    max_len[key] = len(key)
                column = title_list.index(key) + 1
                if max_len.get(key, 0) < len(value):
                    max_len[key] = len(value)
                # print(value)
                try:
                    default_cell = ws.cell(row, column, value)
                    default_cell.style = default_style
                except:
                    print(value)
            ws.row_dimensions[row].height = 17

    # 添加标题行
    for column, key in enumerate(title_list):
        title_cell = ws.cell(1, column + 1, key)
        title_cell.style = title_style
    # ws.row_dimensions[row].height = 18

    # 调整列宽
    for key, _max in max_len.items():
        column_name = get_column_letter(title_list.index(key) + 1)
        ws.column_dimensions[column_name].width = min(int(max_len[key]), 50)

    workbook.save(dst)
    print(f'save success:{dst}')
    return dst


if __name__ == '__main__':
    filename = sys.argv[1]
    if os.path.isdir(filename):
        for f in os.listdir(filename):
            fn = os.path.join(filename, f)
            print(fn)
            dst = tran_to_excel(fn)
            print(dst)
    else:
        tran_to_excel(filename)
